﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Maspalomas
{
   public class ReportingStatements
   {
      #region Consts
      public const string c_ThisMonth = "This month";
      public const string c_ThisMonthGrouped = "This month grouped";
      public const string c_ThisYearMonthByMonth = "This year month by month";
      public const string c_ThisYearMonthByMonthCat = "This year month by month categorized";
      public const string c_PrevMonth = "Prev month";
      public const string c_PrevMonthGrouped = "Prev month grouped";
      public const string c_PivotAnnual = "Pivot annual";
      #endregion
      private List<ReportingStatement> m_Statements = new List<ReportingStatement>();
      public List<ReportingStatement> Statements
      {
         get
         {
            return m_Statements;
         }
      }

      private ReportingStatements()
      {
         //temp initialize...
         m_Statements.Add(new ReportingStatement("All", "SELECT * FROM wydatki"));

         DateTime now = DateTime.Now;
         string startDate = new DateTime(now.Year, now.Month, 1).ToString("yyyy-MM-dd");
         string endDate;
         if(now.Month < 12)
            endDate = new DateTime(now.Year, now.Month + 1, 1).ToString("yyyy-MM-dd");
         else
            endDate = new DateTime(now.Year + 1, 1, 1).ToString("yyyy-MM-dd");
         string statement = string.Format("SELECT * FROM wydatki WHERE date >= '{0}' AND date < '{1}' ORDER BY date", startDate, endDate);
         m_Statements.Add(new ReportingStatement(c_ThisMonth, statement));

         //string statement = string.Format("SELECT category, sum(amount) FROM wydatki WHERE date >= '{0}' AND date < '{1}' GROUP BY category \r\nUNION \r\n(SELECT 'SUMA', sum(amount) FROM wydatki WHERE date >= '{0}' AND date < '{1}') \r\nORDER BY 2", startDate, endDate);
         statement = GetStatement_MonthGroupedByCat(string.Format("WHERE date >= '{0}' AND date < '{1}'", startDate, endDate));
         m_Statements.Add(new ReportingStatement(c_ThisMonthGrouped, statement));

         statement = string.Format("SELECT date_part('year', date) AS year, date_part('month', date) AS month, SUM(amount) suma_wyd FROM wydatki WHERE date_part('year', date) >= {0} GROUP BY date_part('year', date), date_part('month', date) order by 1,2", now.Year);
         m_Statements.Add(new ReportingStatement(c_ThisYearMonthByMonth, statement));

         statement = string.Format("SELECT date_part('year', date) AS year, date_part('month', date) AS month, category category, SUM(amount) suma_wyd FROM wydatki WHERE date_part('year', date) >= {0} GROUP BY date_part('year', date), date_part('month', date), category ORDER BY 3,1,2", now.Year);
         m_Statements.Add(new ReportingStatement(c_ThisYearMonthByMonthCat, statement));

         string prevStartDate;
         if(now.Month > 1)
            prevStartDate = new DateTime(now.Year, now.Month - 1, 1).ToString("yyyy-MM-dd");
         else
            prevStartDate = new DateTime(now.Year-1, 12, 1).ToString("yyyy-MM-dd");
         string prevEndDate = new DateTime(now.Year, now.Month, 1).ToString("yyyy-MM-dd");
         statement = string.Format("SELECT * FROM wydatki WHERE date >= '{0}' AND date < '{1}' ORDER BY date", prevStartDate, prevEndDate);
         m_Statements.Add(new ReportingStatement(c_PrevMonth, statement));

         statement = GetStatement_MonthGroupedByCat(string.Format("WHERE date >= '{0}' AND date < '{1}'", prevStartDate, prevEndDate));
         m_Statements.Add(new ReportingStatement(c_PrevMonthGrouped, statement));

         statement = GetStatement_PivotAnnual(now.Year);
         m_Statements.Add(new ReportingStatement(c_PivotAnnual, statement));
      }

      private static ReportingStatements m_Instance;
      public static ReportingStatements Instance
      {
         get
         {
            if (m_Instance == null)
               m_Instance = new ReportingStatements();
            return m_Instance;
         }
      }

      #region Statements
      public static string GetStatement_All(string whereClause)
      {
         string statement = string.Format("SELECT * FROM wydatki {0} ORDER BY date", whereClause);
         return statement;
      }

      /*public static string GetStatement_ThisMonth(string additionalWhereClause)
      {
         if (additionalWhereClause != string.Empty && !additionalWhereClause.StartsWith("AND"))
            additionalWhereClause = "AND " + additionalWhereClause;
         DateTime now = DateTime.Now;
         string startDate = new DateTime(now.Year, now.Month, 1).ToString("yyyy-MM-dd");
         string endDate = new DateTime(now.Year, now.Month + 1, 1).ToString("yyyy-MM-dd");
         string statement = string.Format("SELECT * FROM wydatki WHERE date >= '{0}' AND date < '{1}' {2} ORDER BY date", startDate, endDate, additionalWhereClause);
         return statement;
      }*/

      public static string CurrentMonthClause
      {
         get
         {
            DateTime now = DateTime.Now;
            string startDate = new DateTime(now.Year, now.Month, 1).ToString("yyyy-MM-dd");
            string endDate = new DateTime(now.Year, now.Month + 1, 1).ToString("yyyy-MM-dd");
            return string.Format("date >= '{0}' AND date < '{1}'", startDate, endDate);
         }
      }

      public static string GetStatement_MonthGroupedByCat(string whereClause)
      {
         string statement = string.Format("SELECT category, sum(amount) FROM wydatki {0} GROUP BY category \r\nUNION \r\n(SELECT 'SUMA', sum(amount) FROM wydatki {0}) \r\nORDER BY 2", whereClause);
         return statement;
      }

      public static string GetStatement_PivotAnnual(int year)
      {
         string statementStub = @"select category, 
                                    SUM(CASE date_part('month', date) WHEN 1 THEN amount ELSE 0 END) as jan{0},
                                    SUM(CASE date_part('month', date) WHEN 2 THEN amount ELSE 0 END) as fab{0},
                                    SUM(CASE date_part('month', date) WHEN 3 THEN amount ELSE 0 END) as mar{0},
                                    SUM(CASE date_part('month', date) WHEN 4 THEN amount ELSE 0 END) as apr{0},
                                    SUM(CASE date_part('month', date) WHEN 5 THEN amount ELSE 0 END) as may{0},
                                    SUM(CASE date_part('month', date) WHEN 6 THEN amount ELSE 0 END) as jun{0},
                                    SUM(CASE date_part('month', date) WHEN 7 THEN amount ELSE 0 END) as jul{0},
                                    SUM(CASE date_part('month', date) WHEN 8 THEN amount ELSE 0 END) as aug{0},
                                    SUM(CASE date_part('month', date) WHEN 9 THEN amount ELSE 0 END) as sep{0},
                                    SUM(CASE date_part('month', date) WHEN 10 THEN amount ELSE 0 END) as oct{0},
                                    SUM(CASE date_part('month', date) WHEN 11 THEN amount ELSE 0 END) as nov{0},
                                    SUM(CASE date_part('month', date) WHEN 12 THEN amount ELSE 0 END) as dec{0} from wydatki
                                WHERE date_part('year', date) = {0}
                                GROUP BY category
                                UNION
                                SELECT 'zzz SUMA', 
                                    SUM(CASE date_part('month', date) WHEN 1 THEN amount ELSE 0 END) as jan{0},
                                    SUM(CASE date_part('month', date) WHEN 2 THEN amount ELSE 0 END) as fab{0},
                                    SUM(CASE date_part('month', date) WHEN 3 THEN amount ELSE 0 END) as mar{0},
                                    SUM(CASE date_part('month', date) WHEN 4 THEN amount ELSE 0 END) as apr{0},
                                    SUM(CASE date_part('month', date) WHEN 5 THEN amount ELSE 0 END) as may{0},
                                    SUM(CASE date_part('month', date) WHEN 6 THEN amount ELSE 0 END) as jun{0},
                                    SUM(CASE date_part('month', date) WHEN 7 THEN amount ELSE 0 END) as jul{0},
                                    SUM(CASE date_part('month', date) WHEN 8 THEN amount ELSE 0 END) as aug{0},
                                    SUM(CASE date_part('month', date) WHEN 9 THEN amount ELSE 0 END) as sep{0},
                                    SUM(CASE date_part('month', date) WHEN 10 THEN amount ELSE 0 END) as oct{0},
                                    SUM(CASE date_part('month', date) WHEN 11 THEN amount ELSE 0 END) as nov{0},
                                    SUM(CASE date_part('month', date) WHEN 12 THEN amount ELSE 0 END) as dec{0} from wydatki
                                WHERE date_part('year', date) = {0}
                                ORDER BY 1";
         string statement = string.Format(statementStub, year);

         return statement;
      }

      public static string GetStatement_FindText(string textToFind, bool useLike, bool caseSensitive)
      {
         string category = caseSensitive ? "category" : "LOWER(category)";
         string subcategory = caseSensitive ? "subcategory" : "LOWER(subcategory)";
         string comment1 = caseSensitive ? "comment" : "LOWER(comment)";
         string comment2 = caseSensitive ? "comment2" : "LOWER(comment2)";
         
         if (!caseSensitive)
            textToFind = textToFind.ToLower();

         string catStmt = string.Format("{0} LIKE '%{1}%'", category, textToFind);
         string subCatStmt = string.Format("{0} LIKE '%{1}%'", subcategory, textToFind);
         string cmt1Stmt = string.Format("{0} LIKE '%{1}%'", comment1, textToFind);
         string cmt2Stmt = string.Format("{0} LIKE '%{1}%'", comment2, textToFind);

         if (!useLike)
         {
            catStmt = string.Format("{0} = '{1}'", category, textToFind);
            subCatStmt = string.Format("{0} = '{1}'", subcategory, textToFind);
            cmt1Stmt = string.Format("{0} = '{1}'", comment1, textToFind);
            cmt2Stmt = string.Format("{0} = '{1}'", comment2, textToFind);
         }

         string statement = string.Format("SELECT * FROM wydatki WHERE {0} OR {1} OR {2} OR {3} \r\nORDER BY 2", catStmt, subCatStmt, cmt1Stmt, cmt2Stmt);

         return statement;
      }

      #endregion
   }

   public class ReportingStatement
   {
      public ReportingStatement(string name, string statement)
      {
         Name = name;
         Statement = statement;
      }

      public string Name { get; set; }
      public string Statement { get; set; }

      public void XmlSave() { }
      public void XmlLoad() { }
   }
}
